package net.sky.report.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import net.sky.report.bean.ClassHour;

public class FuckDao extends Dao{
	private Connection conn = null;
	private PreparedStatement pstmt = null;
	private ResultSet rs = null;
	
	/**
	 * @param teaId
	 * @param terId
	 * @return
	 */
	public List<ClassHour> showClassTime(int teaId,String startTime,String endTime){
		List<ClassHour> list=new ArrayList<ClassHour>();
		ClassHour ch=null;
		try{
			StringBuffer sql=new StringBuffer("select sch.sch_name, t.tea_id,t.tea_name,sb.sub_name,a.gra_id,cl.cla_name,a.att_classsum as classtime,0000 as buketime " +
					"from table(fn_GetTeacherClass("+ teaId +",'"+startTime+"','"+endTime+"')) a " +
					"join bas_subject sb on a.att_sub_id=sb.sub_id " +
					"join bas_school sch on a.sch_id=sch.sch_id " +
					"join bas_teacher t on a.tea_id = t.tea_id " +
					"join bas_class cl on a.att_cla_id = cl.cla_id " +
					"union " +
					"select sch.sch_name, t.tea_id,t.tea_name,sb.sub_name,a.att_gra_id,cl.cla_name,0, " +
					"a.att_hour*1.0/2*count(aas.att_att_stu_id)  as mend_num " +
					"from  att_attendance a " +
					"join att_att_stu aas on a.att_id=aas.att_att_id " +
					"join bas_school sch on a.att_sch_id=sch.sch_id " +
					"join bas_subject sb on a.att_sub_id=sb.sub_id " +
					"join bas_class cl on a.att_cla_id = cl.cla_id " +
					"join bas_teacher t on aas.att_mendtea_id = t.tea_id " +
					"where ATT_INSTEA_ID != aas.att_mendtea_id " +
					"and aas.att_mendtea_id ="+ teaId +" " +
					"and MEND_TIME between to_date('"+startTime+"','yyyy-mm-dd') and to_date('"+endTime+"','yyyy-mm-dd') " +
					"group by sch.sch_name,t.tea_id,t.tea_name,sb.sub_name, a.att_gra_id ,cl.cla_name,att_hour");
			conn=this.getConn();
			pstmt=conn.prepareStatement(sql.toString());
			rs=pstmt.executeQuery();
			while(rs.next()){
				ch=new ClassHour();
				ch.setTeaId(rs.getInt("tea_id"));
				ch.setSchName(rs.getString("sch_name"));
				ch.setGraId(rs.getInt("gra_id"));
				ch.setSubName(rs.getString("sub_name"));
				ch.setClaName(rs.getString("cla_name"));
				ch.setTeaName(rs.getString("tea_name"));
				ch.setClassTime(rs.getDouble("classtime"));
				ch.setBukeTime(rs.getDouble("buketime"));
				list.add(ch);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			this.closeAll(conn, pstmt, rs);
		}
		return list;
	}
}
